/**
 * @project_name staff_vehicle
 * @package_name com.ssh.dao
 * @desc TODO
 * @param
 * @author jack
 * @date 2013-4-24
 * @time 上午9:57:29
 */
package com.ssh.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.ScrollableResults;
import org.hibernate.SessionFactory;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.hibernate.type.StandardBasicTypes;

import com.ssh.entity.StaffApplyCar;
import com.ssh.model.ACarResult;
import com.ssh.model.pdata.StaffDepApplyCarMData;
/**
 * @author jack
 *
 */
public class StaffApplyCarDAOImpl implements StaffApplyCarDAOI {
	private SessionFactory sessionFactory;

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffApplyCarDAOI#queryById(java.lang.String)
	 */
	@Override
	public StaffApplyCar queryById(String id) {
		// TODO Auto-generated method stub
		StaffApplyCar staffApplyCar ;
		try{
			String hql = "from StaffApplyCar as staffApplyCar where staffApplyCar.staffId=?";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString(0, id);
			staffApplyCar = (StaffApplyCar) query.uniqueResult();
		}
		catch(Exception e){
			return null;
		}
		return staffApplyCar;
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffApplyCarDAOI#updateById(java.lang.String)
	 */
	@Override
	public StaffApplyCar updateBySAC(StaffApplyCar staffApplyCar) {
		// TODO Auto-generated method stub
		try{
			sessionFactory.getCurrentSession().update(staffApplyCar);
		}
		catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return staffApplyCar ;
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffApplyCarDAOI#deleteById(java.lang.String)
	 */
	@Override
	public int deleteById(String id) {
		// TODO Auto-generated method stub
		return 0;
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffApplyCarDAOI#addById(java.lang.String)
	 */
	@Override
	public StaffApplyCar addStaffAC(StaffApplyCar staffApplyCar) {
		// TODO Auto-generated method stub
		try{
			sessionFactory.getCurrentSession().save(staffApplyCar);
		}
		catch(Exception e){
			return null;
		}
		return staffApplyCar;
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffApplyCarDAOI#queryByIdList(java.lang.String)
	 */
	@Override
	public List<StaffApplyCar> queryByIdList(String strings) {
		// TODO Auto-generated method stub
		List<StaffApplyCar> staffACList ;
		try{
			String hql = "from StaffApplyCar as staffApplyCar where staffApplyCar.staffId=?";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString(0, strings);
			staffACList = query.list();
		}
		catch(Exception e){
			return null;
		}
		return staffACList;
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffApplyCarDAOI#countById(java.lang.String)
	 */
	@Override
	public int countById(String id) {
		// TODO Auto-generated method stub
		return 0;
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffApplyCarDAOI#addStaffACList(java.util.List)
	 */
	@Override
	public int addStaffACList(List<StaffApplyCar> staffACList) {
		// TODO Auto-generated method stub
		int length = staffACList.size() ;
		try{
			for(int i=0;i<length;i++){
				addStaffAC(staffACList.get(i));
			}
		}
		catch(Exception e){
			return 0 ;
		}
		return length;
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffApplyCarDAOI#queryMyApplyCarInfo(java.lang.String)
	 */
	@Override
	public List<ACarResult> queryMyApplyCarInfo(String staffId) {
		// TODO Auto-generated method stub
		String sql ="select a.staff_id as staffId ," +
				"a.apply_date as applyDate,a.reviewer_id as reviewerId," +
				"a.r_comments as RComments, u.driver_id as driverId," +
				"u.vehicle_id as vehicleId,i.type as type " +
				"FROM staff_apply_car AS a ,staff_use_car as u ,inner_vehicle AS i " +
				"WHERE a.staff_id=u.staff_id AND u.vehicle_id=i.vehicle_id";
		SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql) ;
		query.addScalar("staffId", StandardBasicTypes.STRING);
		query.addScalar("applyDate", StandardBasicTypes.STRING);
		query.addScalar("reviewerId", StandardBasicTypes.STRING);
		query.addScalar("RComments", StandardBasicTypes.STRING);
		query.addScalar("driverId", StandardBasicTypes.STRING);
		query.addScalar("vehicleId", StandardBasicTypes.STRING);
		query.addScalar("type", StandardBasicTypes.STRING);
		query.setResultTransformer(new AliasToBeanResultTransformer(ACarResult.class));
		@SuppressWarnings("unchecked")
		List<ACarResult> aCarResult = query.list();
		return aCarResult;
	}

	/**
	 * @return the sessionFactory
	 */
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	/**
	 * @param sessionFactory the sessionFactory to set
	 */
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	@Override
	public StaffApplyCar queryById(String idWhoApply, String idWhoUse) {
		// TODO Auto-generated method stub
		String hql="from StaffApplyCar as s " +
				"where s.staffId=:idWhoApply and s.usercarStaffid=:idWhoUse and s.applyResult=:applyResult";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter("idWhoApply", idWhoApply).setParameter("idWhoUse", idWhoUse).setParameter("applyResult", "waiting");
		return (StaffApplyCar) query.uniqueResult();
	}
//
	@Override
	public StaffApplyCar queryById(int idstaffApplyCar) {
		// TODO Auto-generated method stub
		String hql="from StaffApplyCar as s " +
				"where s.idstaffApplyCar=:idstaffApplyCar and s.applyResult=:applyResult";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter("idstaffApplyCar", idstaffApplyCar).setParameter("applyResult", "waiting");
		return (StaffApplyCar) query.uniqueResult();
	}

	
	@SuppressWarnings("unchecked")
	@Override
	public List<StaffDepApplyCarMData> getAllApplyCar() {
		// TODO Auto-generated method stub
//		condition where s.applyResult=:applyResult setParameter("applyResult", "waiting").
		String hql="select new com.ssh.model.pdata.StaffDepApplyCarMData(sa,su,sap )" +
				" from StaffApplyCar as sap,Staff as sa,Staff as su " +
				" where sa.staffId=sap.staffId and sap.usercarStaffid = su.staffId and sap.applyResult=:applyResult";
		List<StaffDepApplyCarMData> staffApplyCarList = sessionFactory.getCurrentSession().
				createQuery(hql).setParameter("applyResult", "waiting").list();
		return staffApplyCarList;

	/*
	 * 	String sql = " select newtab1.*,newtab2.uTel "+
					" from(select sc.*, s.staff_tel aTel"+
						" from staff s, staff_apply_car sc where s.staff_id = sc.staff_id) newtab1"+
					" join "+
					" (select sc.staff_id staff_id,sc.usercar_staffid usercar_staffid, s.staff_tel uTel"+
						" from staff s, staff_apply_car sc where s.staff_id = sc.usercar_staffid) newtab2"+
					" on newtab1.staff_id = newtab2.staff_id and newtab1.usercar_staffid = newtab2.usercar_staffid";
		Query query=sessionFactory.getCurrentSession().createSQLQuery(sql);
		ScrollableResults rs = query.scroll();
		List<StaffAMPData> staffApplyCarList = new ArrayList<StaffAMPData>();
		while(rs.next()){
			StaffAMPData s = new StaffAMPData(nullToStr(rs.get(0)),nullToStr(rs.get(1)),nullToStr(rs.get(2)),
					nullToStr(rs.get(3)),nullToStr(rs.get(4)),nullToStr(rs.get(5)),nullToStr(rs.get(6)),
					nullToStr(rs.get(7)),nullToStr(rs.get(8)),nullToStr(rs.get(9)),nullToStr(rs.get(10)),
					nullToStr(rs.get(11))
				);
			staffApplyCarList.add(s);
		}
		return staffApplyCarList;
	}

	private String nullToStr(Object o){
		if(null == o) return "";
		return o.toString();
	}
*/
}

	/** 查看某位员工申请*/
	@SuppressWarnings("unchecked")
	@Override
	public List<StaffDepApplyCarMData> getOneWaitingApplyCar(String idWhoApply, String idWhoUse,String applyResult) {
		// TODO Auto-generated method stub
//		condition where s.applyResult=:applyResult setParameter("applyResult", "waiting").
		String hql="select new com.ssh.model.pdata.StaffDepApplyCarMData(sa,su,sap ) " +
				" from StaffApplyCar as sap,Staff as sa,Staff as su " +
				" where sa.staffId=sap.staffId and sap.usercarStaffid = su.staffId " +
				" and sap.staffId=:idWhoApply and sap.usercarStaffid=:idWhoUse and sap.applyResult=:applyResult";
		List<StaffDepApplyCarMData> staffApplyCarList = sessionFactory.getCurrentSession().
				createQuery(hql).setParameter("idWhoApply", idWhoApply).
				setParameter("idWhoUse", idWhoUse).setParameter("applyResult", applyResult).list();
		return staffApplyCarList;
}

}